﻿using NPOI.HPSF;
using NPOI.HSSF.UserModel;
using NPOI.HSSF.Util;
using NPOI.SS.UserModel;
using NPOI.SS.Util;
using NPOI.XSSF.UserModel;
using System;
using System.Collections.Generic;
using System.Data;
using System.IO;
using System.Linq;
using System.Reflection;
using System.Text;
using System.Threading.Tasks;
using System.Web;

namespace DevelopAssistant.Common
{
    public class NpoiHelper
    {
        #region NPIO 操作Excel

        private static string DecimalToFormat(string value)
        {
            if (!string.IsNullOrEmpty(value) && value != "0")
            {
                value = Math.Round(decimal.Parse(value), 2).ToString("0.00");
                if (value.Equals("0.00"))
                {
                    value = "≈0";
                }
            }
            return value;
        }

        private static string PercentageToFormat(string v, string value)
        {
            if (!string.IsNullOrEmpty(v) && v != "0")
            {
                if (!string.IsNullOrEmpty(value))
                {
                    value = Math.Round((decimal.Parse(value) * 100), 2).ToString("0.00");
                    if (value.Equals("0.00"))
                    {
                        value = "≈0";
                    }
                    else
                    {
                        value = value + "%";
                    }
                }
            }
            else
            {
                value = "0%";
            }
            return value;
        }

        private static string GetSheetColumnName(int num)
        {
            Dictionary<int, string> di = new Dictionary<int, string>();
            di.Add(0, "A");
            di.Add(1, "B");
            di.Add(2, "C");
            di.Add(3, "D");
            di.Add(4, "E");
            di.Add(5, "F");
            di.Add(6, "G");
            di.Add(7, "H");
            di.Add(8, "I");
            di.Add(9, "J");
            di.Add(10, "K");
            di.Add(11, "L");
            di.Add(12, "M");
            di.Add(13, "N");
            di.Add(14, "O");
            di.Add(15, "P");
            di.Add(16, "Q");
            di.Add(17, "R");
            di.Add(18, "S");
            di.Add(19, "T");
            di.Add(20, "U");
            di.Add(21, "V");
            di.Add(22, "W");
            di.Add(23, "X");
            di.Add(24, "Y");
            di.Add(25, "Z");

            return di[num];
        }

        private static string AddDataColumn(DataTable dt, string columnname, ref int index,ref bool goon)
        {
            string val = columnname;                   
            if (dt.Columns != null && dt.Columns.Count > 0 && dt.Columns.Contains(columnname))
            {
                foreach (DataColumn dc in dt.Columns)
                {
                    if (dc.ColumnName.Equals(columnname))
                    {
                        index++;
                    }
                }
            }
            if (index > 0)
            {
                if (columnname.Contains("["))
                    columnname = columnname.Substring(0, columnname.LastIndexOf("["));
                val = columnname+"[" + index+"]";
            }

            #region 递规调用
            if (!dt.Columns.Contains(val))
            {
                goon = false;
                dt.Columns.Add(val, typeof(string));
            }
            while (goon)
            {               
                AddDataColumn(dt, val, ref index, ref goon);
            }
            #endregion

            return val;
        }

        private static Dictionary<string, ICellStyle> CreateSheetCellStyles(HSSFWorkbook Workbook)
        {
            Dictionary<string, ICellStyle> CellStyles = new Dictionary<string, ICellStyle>();

            IFont Font = Workbook.CreateFont();

            ICellStyle cellcstyle = Workbook.CreateCellStyle();
            cellcstyle.Alignment = NPOI.SS.UserModel.HorizontalAlignment.CENTER;
            cellcstyle.VerticalAlignment = NPOI.SS.UserModel.VerticalAlignment.CENTER;
            cellcstyle.BorderBottom = NPOI.SS.UserModel.BorderStyle.THIN;
            cellcstyle.BorderLeft = NPOI.SS.UserModel.BorderStyle.THIN;
            cellcstyle.BorderRight = NPOI.SS.UserModel.BorderStyle.THIN;
            cellcstyle.BorderTop = NPOI.SS.UserModel.BorderStyle.THIN;
            cellcstyle.BottomBorderColor = HSSFColor.BLACK.index;
            cellcstyle.LeftBorderColor = HSSFColor.BLACK.index;
            cellcstyle.RightBorderColor = HSSFColor.BLACK.index;
            cellcstyle.TopBorderColor = HSSFColor.BLACK.index;
            cellcstyle.WrapText = true;

            Font = Workbook.CreateFont();            
            Font.FontHeightInPoints = 11;
            cellcstyle.SetFont(Font);

            CellStyles.Add("BodyCell", cellcstyle);

            ICellStyle cellcbstyle = Workbook.CreateCellStyle();
            cellcbstyle.Alignment = NPOI.SS.UserModel.HorizontalAlignment.CENTER;
            cellcbstyle.VerticalAlignment = NPOI.SS.UserModel.VerticalAlignment.CENTER;
            cellcbstyle.BorderBottom = NPOI.SS.UserModel.BorderStyle.THIN;
            cellcbstyle.BorderLeft = NPOI.SS.UserModel.BorderStyle.THIN;
            cellcbstyle.BorderRight = NPOI.SS.UserModel.BorderStyle.THIN;
            cellcbstyle.BorderTop = NPOI.SS.UserModel.BorderStyle.THIN;
            cellcbstyle.BottomBorderColor = HSSFColor.BLACK.index;
            cellcbstyle.LeftBorderColor = HSSFColor.BLACK.index;
            cellcbstyle.RightBorderColor = HSSFColor.BLACK.index;
            cellcbstyle.TopBorderColor = HSSFColor.BLACK.index;
            cellcbstyle.WrapText = true;

            Font = Workbook.CreateFont();
            Font.Boldweight = (short)FontBoldWeight.BOLD;
            cellcbstyle.SetFont(Font);

            CellStyles.Add("BodyBoldCell", cellcbstyle);

            ICellStyle cellhstyle = Workbook.CreateCellStyle();
            cellhstyle.Alignment = NPOI.SS.UserModel.HorizontalAlignment.CENTER;
            cellhstyle.VerticalAlignment = NPOI.SS.UserModel.VerticalAlignment.CENTER;
            cellhstyle.BorderBottom = NPOI.SS.UserModel.BorderStyle.THIN;
            cellhstyle.BorderLeft = NPOI.SS.UserModel.BorderStyle.THIN;
            cellhstyle.BorderRight = NPOI.SS.UserModel.BorderStyle.THIN;
            cellhstyle.BorderTop = NPOI.SS.UserModel.BorderStyle.THIN;
            cellhstyle.BottomBorderColor = HSSFColor.BLACK.index;
            cellhstyle.LeftBorderColor = HSSFColor.BLACK.index;
            cellhstyle.RightBorderColor = HSSFColor.BLACK.index;
            cellhstyle.TopBorderColor = HSSFColor.BLACK.index;

            Font = Workbook.CreateFont();
            //Font.Color = HSSFColor.RED.index;
            Font.Boldweight = (short)FontBoldWeight.BOLD;
            Font.FontHeightInPoints = 10;
            cellhstyle.SetFont(Font);

            cellhstyle.FillForegroundColor = HSSFColor.PALE_BLUE.index; //GetNPOIColor(Workbook, System.Drawing.Color.Orange); //HSSFColor.YELLOW.index;
            cellhstyle.FillBackgroundColor = HSSFColor.PALE_BLUE.index; //GetNPOIColor(Workbook, System.Drawing.Color.YellowGreen);//HSSFColor.YELLOW.index;
            cellhstyle.FillPattern = FillPatternType.SOLID_FOREGROUND;

            CellStyles.Add("HeadCell", cellhstyle);

            ICellStyle celltstyle = Workbook.CreateCellStyle();
            celltstyle.Alignment = NPOI.SS.UserModel.HorizontalAlignment.RIGHT;
            celltstyle.VerticalAlignment = NPOI.SS.UserModel.VerticalAlignment.CENTER;

            CellStyles.Add("DateCell", celltstyle);

            ICellStyle cellttstyle = Workbook.CreateCellStyle();
            cellttstyle.Alignment = NPOI.SS.UserModel.HorizontalAlignment.CENTER;
            cellttstyle.VerticalAlignment = NPOI.SS.UserModel.VerticalAlignment.CENTER;

            Font = Workbook.CreateFont();
            Font.Boldweight = (short)FontBoldWeight.BOLD;
            Font.FontHeightInPoints = 12;
            cellttstyle.SetFont(Font);
            CellStyles.Add("TitleCell", cellttstyle);

            return CellStyles;
        }

        private static ICellStyle CreateSheetCellStyle(HSSFWorkbook Workbook)
        {
            ICellStyle CellStyle = Workbook.CreateCellStyle();

            CellStyle.Alignment = NPOI.SS.UserModel.HorizontalAlignment.CENTER;
            CellStyle.VerticalAlignment = NPOI.SS.UserModel.VerticalAlignment.CENTER;
            CellStyle.BorderBottom = NPOI.SS.UserModel.BorderStyle.THIN;
            CellStyle.BorderLeft = NPOI.SS.UserModel.BorderStyle.THIN;
            CellStyle.BorderRight = NPOI.SS.UserModel.BorderStyle.THIN;
            CellStyle.BorderTop = NPOI.SS.UserModel.BorderStyle.THIN;
            CellStyle.BottomBorderColor = HSSFColor.BLACK.index;
            CellStyle.LeftBorderColor = HSSFColor.BLACK.index;
            CellStyle.RightBorderColor = HSSFColor.BLACK.index;
            CellStyle.TopBorderColor = HSSFColor.BLACK.index;
            CellStyle.WrapText = true;

            return CellStyle;
        }

        private static ICellStyle CellStyleClone(HSSFWorkbook Workbook, ICellStyle CellStyle)
        {
            ICellStyle _cellstyle = Workbook.CreateCellStyle();
            PropertyInfo[] properties = _cellstyle.GetType().GetProperties();

            foreach (PropertyInfo fi in properties)
            {
                object obj = fi.GetValue(CellStyle);
                if (obj != null && obj.GetType().Equals(fi.PropertyType) && fi.CanWrite)
                {
                    fi.SetValue(_cellstyle, obj, null);
                }
            }

            _cellstyle.SetFont(CellStyle.GetFont(Workbook));

            return _cellstyle;
        }

        private static short GetNPOIColor(HSSFWorkbook Workbook, System.Drawing.Color SystemColor)
        {
            short s = 0;
            HSSFPalette XlPalette = Workbook.GetCustomPalette();
            HSSFColor XlColour = XlPalette.FindColor(SystemColor.R, SystemColor.G, SystemColor.B);
            if (XlColour == null)
            {
                if (NPOI.HSSF.Record.PaletteRecord.STANDARD_PALETTE_SIZE < 255)
                {
                    if (NPOI.HSSF.Record.PaletteRecord.STANDARD_PALETTE_SIZE < 64)
                    {
                        NPOI.HSSF.Record.PaletteRecord.STANDARD_PALETTE_SIZE = 64;
                        NPOI.HSSF.Record.PaletteRecord.STANDARD_PALETTE_SIZE += 1;
                        XlColour = XlPalette.AddColor(SystemColor.R, SystemColor.G, SystemColor.B);
                    }
                    else
                    {
                        XlColour = XlPalette.FindSimilarColor(SystemColor.R, SystemColor.G, SystemColor.B);
                    }
                    s = XlColour.GetIndex();
                }
            }
            else
            {
                s = XlColour.GetIndex();
            }
            return s;
        }
        
        /// <summary>
        /// 支持前7列可合并单元格
        /// </summary>
        /// <param name="TemplatePath"></param>
        /// <param name="Merge"></param>
        /// <param name="DataSource"></param>
        /// <param name="Foot"></param>
        /// <returns></returns>
        private static HSSFWorkbook CreateNPIOExcel(String TemplatePath,bool Merge, DataTable DataSource, DataTable Foot)
        {
            string filetype;

            HSSFWorkbook Workbook = (HSSFWorkbook)TemplateNPIOExcel(TemplatePath, out filetype);
            ISheet Sheet1 = Workbook.GetSheetAt(0);

            #region 初始化
            ////create a entry of DocumentSummaryInformation
            DocumentSummaryInformation dsi = PropertySetFactory.CreateDocumentSummaryInformation();
            dsi.Company = "上海合驿物流有限公司";
            Workbook.DocumentSummaryInformation = dsi;

            ////create a entry of SummaryInformation
            SummaryInformation si = PropertySetFactory.CreateSummaryInformation();
            si.Subject = "报表导出";
            Workbook.SummaryInformation = si;
            #endregion

            #region 创建样式

            ICellStyle body_cellstyle = CreateSheetCellStyle(Workbook);
            ICellStyle bodybold_cellstyle = CellStyleClone(Workbook, body_cellstyle);  //CellStyles["BodyBoldCell"];//内容加粗样式
            IFont Font = Workbook.CreateFont();
            Font.Boldweight = (short)FontBoldWeight.BOLD;
            bodybold_cellstyle.SetFont(Font);
            ICellStyle footbold_cellstyle = CellStyleClone(Workbook, body_cellstyle); //CellStyles["RatioCell"];//表尾百分比样式
            footbold_cellstyle.SetFont(Font);
            footbold_cellstyle.DataFormat = HSSFDataFormat.GetBuiltinFormat("0.00%");

            #endregion

            int rowIndex = 0;
            int colIndex = 0;

            System.Collections.IEnumerator rows = Sheet1.GetRowEnumerator();
            while (rows.MoveNext())
            {
                rowIndex++;
            }           

            IDataFormat datastyle = Workbook.CreateDataFormat();

            int _rowNumber = 1; int _rowTotal = DataSource.Rows.Count;

            DataRow _temprow = null;

            foreach (DataRow dr in DataSource.Rows)
            {
                IRow row = Sheet1.CreateRow(rowIndex);
                colIndex = 0;                

                foreach (DataColumn dc in DataSource.Columns)
                {
                    ICell cell = row.CreateCell(colIndex);
                    cell.CellStyle = body_cellstyle;

                    string colname = dc.ColumnName;
                    string value = dr[colname] + "";

                    switch (dc.DataType.Name)
                    {
                        case "DateTime":
                            if (!string.IsNullOrEmpty(value))
                            {
                                value = DateTime.Parse(value).ToString("yyyy-MM-dd");
                                cell.SetCellValue(value);
                            }
                            break;
                        case "Int16":
                        case "Int32":
                        case "Int64":
                            if (!string.IsNullOrEmpty(value))
                                cell.SetCellValue(Int32.Parse(value));
                            break;
                        case "Single":
                        case "Double":
                        case "Decimal":
                            if (colname.Contains("比例") || colname.Contains("占比") || colname.Contains("百分比"))
                            {
                                cell.SetCellValue(PercentageToFormat("23", value));
                            }
                            else if (!string.IsNullOrEmpty(value))
                            {
                                cell.SetCellValue(Math.Round(double.Parse(value), 2));//DecimalToFormat(value);
                            }
                            break;
                        case "byte[]":
                        case "Byte[]":
                            if (dr[colname] != null && dr[colname] != DBNull.Value)
                            {
                                StringBuilder sb = new StringBuilder();
                                Byte[] array = (Byte[])dr[colname];
                                int maxlen = array.Length;
                                if (maxlen > 16)
                                {
                                    maxlen = 16;
                                }
                                for (int i = 0; i < maxlen; i++)
                                {
                                    sb.Append(array[i].ToString("X2"));
                                }
                                value = "Ox" + sb.ToString();
                                maxlen = array.Length;
                                if (maxlen > 16)
                                {
                                    value += "...";
                                }
                                cell.SetCellValue(value);
                            }
                            break;
                        default:
                            cell.SetCellValue(value);
                            break;
                    }

                    if (Merge)
                    {
                        if (_temprow != null && dc.DataType.Name.Equals("String") && (_temprow[colname] + "").Equals(value))
                        {
                            switch (colIndex)
                            {
                                case 0:
                                    Sheet1.AddMergedRegion(new CellRangeAddress(rowIndex - 1, rowIndex, colIndex, colIndex));
                                    break;
                                case 1:
                                    if ((_temprow[colIndex - 1] + "").Equals(dr[colIndex - 1] + ""))
                                    {
                                        Sheet1.AddMergedRegion(new CellRangeAddress(rowIndex - 1, rowIndex, colIndex, colIndex));
                                    }
                                    break;
                                case 2:
                                    if ((_temprow[colIndex - 1] + "").Equals(dr[colIndex - 1] + "") 
                                        && (_temprow[colIndex - 2] + "").Equals(dr[colIndex - 2] + ""))
                                    {
                                        Sheet1.AddMergedRegion(new CellRangeAddress(rowIndex - 1, rowIndex, colIndex, colIndex));
                                    }
                                    break;
                                case 3:
                                    if ((_temprow[colIndex - 1] + "").Equals(dr[colIndex - 1] + "")
                                        && (_temprow[colIndex - 2] + "").Equals(dr[colIndex - 2] + "")
                                         && (_temprow[colIndex - 3] + "").Equals(dr[colIndex - 3] + ""))
                                    {
                                        Sheet1.AddMergedRegion(new CellRangeAddress(rowIndex - 1, rowIndex, colIndex, colIndex));
                                    }
                                    break;
                                case 4:
                                    if ((_temprow[colIndex - 1] + "").Equals(dr[colIndex - 1] + "") 
                                        && (_temprow[colIndex - 2] + "").Equals(dr[colIndex - 2] + "")
                                         && (_temprow[colIndex - 3] + "").Equals(dr[colIndex - 3] + "")
                                          && (_temprow[colIndex - 4] + "").Equals(dr[colIndex - 4] + ""))
                                    {
                                        Sheet1.AddMergedRegion(new CellRangeAddress(rowIndex - 1, rowIndex, colIndex, colIndex));
                                    }
                                    break;
                                case 5:
                                    if ((_temprow[colIndex - 1] + "").Equals(dr[colIndex - 1] + "")
                                        && (_temprow[colIndex - 2] + "").Equals(dr[colIndex - 2] + "")
                                         && (_temprow[colIndex - 3] + "").Equals(dr[colIndex - 3] + "")
                                          && (_temprow[colIndex - 4] + "").Equals(dr[colIndex - 4] + "")
                                           && (_temprow[colIndex - 5] + "").Equals(dr[colIndex - 5] + ""))
                                    {
                                        Sheet1.AddMergedRegion(new CellRangeAddress(rowIndex - 1, rowIndex, colIndex, colIndex));
                                    }
                                    break;
                                case 6:
                                    if ((_temprow[colIndex - 1] + "").Equals(dr[colIndex - 1] + "")
                                        && (_temprow[colIndex - 2] + "").Equals(dr[colIndex - 2] + "")
                                         && (_temprow[colIndex - 3] + "").Equals(dr[colIndex - 3] + "")
                                          && (_temprow[colIndex - 4] + "").Equals(dr[colIndex - 4] + "")
                                           && (_temprow[colIndex - 5] + "").Equals(dr[colIndex - 5] + "")
                                            && (_temprow[colIndex - 6] + "").Equals(dr[colIndex - 6] + ""))
                                    {
                                        Sheet1.AddMergedRegion(new CellRangeAddress(rowIndex - 1, rowIndex, colIndex, colIndex));
                                    }
                                    break;
                                case 7:
                                    if ((_temprow[colIndex - 1] + "").Equals(dr[colIndex - 1] + "")
                                        && (_temprow[colIndex - 2] + "").Equals(dr[colIndex - 2] + "")
                                         && (_temprow[colIndex - 3] + "").Equals(dr[colIndex - 3] + "")
                                          && (_temprow[colIndex - 4] + "").Equals(dr[colIndex - 4] + "")
                                           && (_temprow[colIndex - 5] + "").Equals(dr[colIndex - 5] + "")
                                            && (_temprow[colIndex - 6] + "").Equals(dr[colIndex - 6] + "")
                                              && (_temprow[colIndex - 7] + "").Equals(dr[colIndex - 7] + ""))
                                    {
                                        Sheet1.AddMergedRegion(new CellRangeAddress(rowIndex - 1, rowIndex, colIndex, colIndex));
                                    }
                                    break;
                            }
  
                        }
                    }

                    colIndex++;
                }

                if (Merge)
                {
                    _temprow = dr;
                }

                rowIndex++;
                _rowNumber++;

            }

            if (Foot != null)
            {
                foreach (DataRow dr in Foot.Rows)
                {
                    IRow row = Sheet1.CreateRow(rowIndex);
                    colIndex = 0;
                    row.Height = 20 * 20;

                    foreach (DataColumn dc in Foot.Columns)
                    {
                        ICell cell = row.CreateCell(colIndex);
                        cell.CellStyle = bodybold_cellstyle;

                        string colname = dc.ColumnName;
                        string value = dr[colname] + "";

                        if (!string.IsNullOrEmpty(value))
                        {
                            if (value.StartsWith("fn:"))
                            {
                                //string fun = "sum(" + GetSheetColumnName(colIndex) + "" + (3 + 1) + ":" + GetSheetColumnName(colIndex) + "" + rowIndex + ")"; 自动计算
                                string fun = value.Replace("fn:", "").Replace("{", "").Replace("}", "").Replace("?", "" + rowIndex + "");
                                if (colname.Contains("比例") || colname.Contains("占比") || colname.Contains("百分比"))
                                {
                                    cell.CellStyle = footbold_cellstyle;
                                    cell.SetCellFormula(fun); //利用公式来统计                                   
                                }
                                else
                                {
                                    cell.SetCellFormula(fun); //利用公式来统计
                                }

                            }
                            if (value.StartsWith("vn:"))
                            {
                                value = value.Replace("vn:", "");
                                cell.SetCellValue(value);
                            }
                        }
                        else
                        {
                            cell.SetCellValue("");
                        }

                        colIndex++;
                    }
                }
            }           

            return Workbook;
        }

        private static HSSFWorkbook CreateNPIOExcel(String SheetName, String Title, DataTable DataSource, DataTable Foot)
        {
            HSSFWorkbook Workbook = new HSSFWorkbook();
            ISheet Sheet1 = Workbook.CreateSheet();

            #region 初始化
            ////create a entry of DocumentSummaryInformation
            DocumentSummaryInformation dsi = PropertySetFactory.CreateDocumentSummaryInformation();
            dsi.Company = "上海合驿物流有限公司";
            Workbook.DocumentSummaryInformation = dsi;

            ////create a entry of SummaryInformation
            SummaryInformation si = PropertySetFactory.CreateSummaryInformation();
            si.Subject = "报表导出";
            Workbook.SummaryInformation = si;
            #endregion

            int rowIndex = 0;
            int colIndex = 0;

            Dictionary<string, ICellStyle> CellStyles = CreateSheetCellStyles(Workbook);

            ICellStyle head_cellstyle = CellStyles["HeadCell"];//表头样式
            ICellStyle body_cellstyle = CellStyles["BodyCell"];//内容样式            
            ICellStyle date_cellstyle = CellStyles["DateCell"];//导出时间样式
            ICellStyle title_cellstyle = CellStyles["TitleCell"];//标题样式
            ICellStyle bodybold_cellstyle = CellStyleClone(Workbook, body_cellstyle);  //CellStyles["BodyBoldCell"];//内容加粗样式
            IFont Font = Workbook.CreateFont();
            Font.Boldweight = (short)FontBoldWeight.BOLD;
            bodybold_cellstyle.SetFont(Font);
            ICellStyle footbold_cellstyle = CellStyleClone(Workbook, body_cellstyle); //CellStyles["RatioCell"];//表尾百分比样式
            footbold_cellstyle.SetFont(Font);
            footbold_cellstyle.DataFormat = HSSFDataFormat.GetBuiltinFormat("0.00%");

            IRow trow = Sheet1.CreateRow(rowIndex);
            trow.Height = 18 * 20;
            ICell tcell = trow.CreateCell(colIndex);
            tcell.SetCellValue(Title);
            tcell.CellStyle = title_cellstyle;
            Sheet1.AddMergedRegion(new CellRangeAddress(rowIndex, rowIndex, colIndex, DataSource.Columns.Count - 1));
            rowIndex++;

            trow = Sheet1.CreateRow(rowIndex);
            trow.Height = 18 * 20;
            tcell = trow.CreateCell(colIndex);
            tcell.SetCellValue("导出时间：" + DateTime.Now.ToString("yyyy-MM-dd HH:mm"));
            tcell.CellStyle = date_cellstyle;
            Sheet1.AddMergedRegion(new CellRangeAddress(rowIndex, rowIndex, colIndex, DataSource.Columns.Count - 1));
            rowIndex++;

            IRow hrow = Sheet1.CreateRow(rowIndex);
            hrow.Height = 20 * 20;
            foreach (DataColumn dc in DataSource.Columns)
            {
                ICell hcell = hrow.CreateCell(colIndex, CellType.STRING);
                hcell.CellStyle = head_cellstyle;
                hcell.SetCellValue(dc.ColumnName);
                Sheet1.SetColumnWidth(colIndex, 20 * 256);
                colIndex++;
            }
            rowIndex++;

            IDataFormat datastyle = Workbook.CreateDataFormat();

            int _rowNumber = 1; int _rowTotal = DataSource.Rows.Count;

            foreach (DataRow dr in DataSource.Rows)
            {
                IRow row = Sheet1.CreateRow(rowIndex);
                row.Height = 18 * 20;
                colIndex = 0;

                foreach (DataColumn dc in DataSource.Columns)
                {
                    ICell cell = row.CreateCell(colIndex);
                    cell.CellStyle = body_cellstyle;

                    string colname = dc.ColumnName;
                    string value = dr[colname] + "";

                    switch (dc.DataType.Name)
                    {
                        case "DateTime":
                            if (!string.IsNullOrEmpty(value))
                            {
                                value = DateTime.Parse(value).ToString("yyyy-MM-dd");
                                //cell.CellStyle.DataFormat = datastyle.GetFormat("yyyy-MM-dd");
                                cell.SetCellValue(value);
                            }
                            break;
                        case "Int16":
                        case "Int32":
                        case "Int64":
                            if (!string.IsNullOrEmpty(value))
                                cell.SetCellValue(Int32.Parse(value));
                            break;
                        case "Single":
                        case "Double":
                        case "Decimal":
                            if (colname.Contains("比例") || colname.Contains("占比") || colname.Contains("百分比"))
                            {
                                cell.SetCellValue(PercentageToFormat("23", value));
                            }
                            else if (!string.IsNullOrEmpty(value))
                            {
                                cell.SetCellValue(Math.Round(double.Parse(value), 2));//DecimalToFormat(value);
                            }
                            break;
                        case "byte[]":
                        case "Byte[]":
                            if (dr[colname] != null && dr[colname] != DBNull.Value)
                            {
                                StringBuilder sb = new StringBuilder();
                                Byte[] array = (Byte[])dr[colname];
                                int maxlen = array.Length;
                                if (maxlen > 16)
                                {
                                    maxlen = 16;
                                }
                                for (int i = 0; i < maxlen; i++)
                                {
                                    sb.Append(array[i].ToString("X2"));
                                }
                                value = "Ox" + sb.ToString();
                                maxlen = array.Length;
                                if (maxlen > 16)
                                {
                                    value += "...";
                                }
                                cell.SetCellValue(value);
                            }
                            break;
                        default:
                            cell.SetCellValue(value);
                            break;
                    }

                    colIndex++;
                }


                rowIndex++;
                _rowNumber++;

            }

            if (Foot != null)
            {
                foreach (DataRow dr in Foot.Rows)
                {
                    IRow row = Sheet1.CreateRow(rowIndex);
                    colIndex = 0;
                    row.Height = 20 * 20;

                    foreach (DataColumn dc in Foot.Columns)
                    {
                        ICell cell = row.CreateCell(colIndex);
                        cell.CellStyle = bodybold_cellstyle;

                        string colname = dc.ColumnName;
                        string value = dr[colname] + "";

                        if (!string.IsNullOrEmpty(value))
                        {
                            if (value.StartsWith("fn:"))
                            {
                                //string fun = "sum(" + GetSheetColumnName(colIndex) + "" + (3 + 1) + ":" + GetSheetColumnName(colIndex) + "" + rowIndex + ")"; 自动计算
                                string fun = value.Replace("fn:", "").Replace("{", "").Replace("}", "").Replace("?", "" + rowIndex + "");
                                if (colname.Contains("比例") || colname.Contains("占比") || colname.Contains("百分比"))
                                {
                                    cell.CellStyle = footbold_cellstyle;
                                    cell.SetCellFormula(fun); //利用公式来统计                                   
                                }
                                else
                                {
                                    cell.SetCellFormula(fun); //利用公式来统计
                                }

                            }
                            if (value.StartsWith("vn:"))
                            {
                                value = value.Replace("vn:", "");
                                cell.SetCellValue(value);
                            }
                        }
                        else
                        {
                            cell.SetCellValue("");
                        }

                        colIndex++;
                    }
                }
            }

            CellStyles.Clear();

            return Workbook;
        }

        /// <summary>
        /// 创建默认单元格样式
        /// </summary>
        /// <param name="Workbook"></param>
        /// <returns></returns>
        public static ICellStyle CreateDefaultSheetCellStyle(HSSFWorkbook Workbook)
        {
            return CreateSheetCellStyle(Workbook);
        }

        /// <summary>
        /// 读取Excel数据
        /// </summary>
        /// <param name="ExcelPath"></param>
        /// <param name="SheetName"></param>
        /// <returns></returns>
        public static DataTable GetTable(string ExcelPath, string SheetName)
        {
            DataTable dt = new DataTable();

            string filetype;
            IWorkbook work = TemplateNPIOExcel(ExcelPath, out filetype);
            ISheet sheet = null;

            if (!string.IsNullOrEmpty(SheetName))
            {
                sheet = work.GetSheet(SheetName);
            }
            else
            {
                sheet = work.GetSheetAt(0);
            }

            int rowIndex = 0;
            System.Collections.IEnumerator rows = sheet.GetRowEnumerator();
           
            while (rows.MoveNext())
            {
                IRow row = (IRow)rows.Current;

                if (rowIndex == 0)
                {
                    System.Collections.IEnumerator columns = row.GetEnumerator();
                    while (columns.MoveNext())
                    {
                        ICell cell = (ICell)columns.Current;
                        int index = 0;
                        bool goon = true;
                        AddDataColumn(dt, cell.StringCellValue, ref index, ref goon);                         
                    }
                }
                else
                {
                    DataRow dr = dt.NewRow();
                    int columnIndex = 0;
                    foreach (DataColumn dc in dt.Columns)
                    {
                        ICell cell = row.GetCell(columnIndex);
                        if (cell != null)
                        {
                            switch (cell.CellType)
                            {                               
                                case CellType.NUMERIC: dr[dc.ColumnName] = cell.NumericCellValue; break;
                                case CellType.BOOLEAN: dr[dc.ColumnName] = cell.BooleanCellValue; break;
                                case CellType.Unknown: dr[dc.ColumnName] = cell.StringCellValue; break;
                                case CellType.STRING: 
                                default :
                                    dr[dc.ColumnName] = cell.StringCellValue; break;
                            }

                            if (cell.CellType.Equals(CellType.NUMERIC) && DateUtil.IsCellDateFormatted(cell))
                            {
                                dr[dc.ColumnName] = cell.DateCellValue;
                            }
                        }
                        columnIndex++;
                    }

                    dt.Rows.Add(dr);
                }

                rowIndex++;
            }

            return dt;
        }

        /// <summary>
        /// 复制表结构
        /// </summary>
        /// <param name="DataSorce"></param>
        /// <returns></returns>
        public static DataTable TableClone(DataTable DataSorce)
        {
            DataTable dt = new DataTable();
            foreach (DataColumn dc in DataSorce.Columns)
            {
                DataColumn ndc = new DataColumn(dc.ColumnName, typeof(string));
                dt.Columns.Add(ndc);
            }
            return dt;
        }

        /// <summary>
        /// 读取模板
        /// </summary>
        /// <param name="TemplatePath"></param>
        /// <param name="filetype"></param>
        /// <returns></returns>
        public static IWorkbook TemplateNPIOExcel(String TemplatePath, out string filetype)
        {
            IWorkbook Workbook = null; //HSSFWorkbook
            if (!System.IO.File.Exists(TemplatePath))
            {
                throw new Exception("模板不存在");
            }
            filetype = TemplatePath.Substring(TemplatePath.LastIndexOf(".")).ToLower();
            using (FileStream file = new FileStream(TemplatePath, FileMode.Open, FileAccess.Read))
            {
                switch (filetype)
                {
                    case ".xls":
                        Workbook = new HSSFWorkbook(file);
                        break;
                    case ".xlsx":
                        Workbook = new XSSFWorkbook(file);
                        break;
                    default :
                        Workbook = new HSSFWorkbook(file);
                        break;
                }
                file.Close();
            }
            return Workbook;
        }

        /// <summary>
        /// 导出
        /// </summary>
        /// <param name="Context"></param>
        /// <param name="Workbook"></param>
        /// <param name="FileName"></param>
        public static void ExcelOutToPrint(HttpContextBase Context, HSSFWorkbook Workbook, String FileName)
        {
            Context.Response.Charset = "UTF-8";
            Context.Response.ContentType = "application/vnd.ms-excel";
            Context.Response.ContentEncoding = Encoding.UTF8;
            //判断浏览器
            string UserAgent = Context.Request.ServerVariables["http_user_agent"].ToLower();
            FileName = DateTime.Now.ToString("yyyy-MM-dd") + FileName;
            if (UserAgent.IndexOf("firefox") == -1)
            {//非火狐浏览器
                FileName = HttpUtility.UrlEncode(FileName, Encoding.UTF8);
            }
            Context.Response.AddHeader("Content-Disposition", string.Format("attachment;filename={0}", FileName + ".xls"));
            Context.Response.Clear();

            System.IO.MemoryStream file = new System.IO.MemoryStream();
            Workbook.Write(file);
            file.WriteTo(Context.Response.OutputStream);
            Context.Response.End();
        }

        /// <summary>
        /// 自动填充数据并导出
        /// </summary>
        /// <param name="Context"></param>
        /// <param name="DataSource"></param>
        /// <param name="SheetName"></param>
        /// <param name="Title"></param>
        /// <param name="FileName"></param>
        /// <param name="Foot"></param>
        public static void ExcelOutToPrint(HttpContextBase Context, DataTable DataSource, String SheetName, string Title, String FileName, DataTable Foot)
        {
            HSSFWorkbook wb = CreateNPIOExcel(SheetName, Title, DataSource, Foot);
            Context.Response.Charset = "UTF-8";
            Context.Response.ContentType = "application/vnd.ms-excel";
            Context.Response.ContentEncoding = Encoding.UTF8;
            //判断浏览器
            string UserAgent = Context.Request.ServerVariables["http_user_agent"].ToLower();
            FileName = DateTime.Now.ToString("yyyy-MM-dd") + FileName;
            if (UserAgent.IndexOf("firefox") == -1)
            {//非火狐浏览器
                FileName = HttpUtility.UrlEncode(FileName, Encoding.UTF8);
            }
            Context.Response.AddHeader("Content-Disposition", string.Format("attachment;filename={0}", FileName + ".xls"));
            Context.Response.Clear();

            System.IO.MemoryStream file = new System.IO.MemoryStream();
            wb.Write(file);
            file.WriteTo(Context.Response.OutputStream);
            Context.Response.End();
        }
        
        /// <summary>
        /// 读取模板并自动填充数据 并导出
        /// </summary>
        /// <param name="Context"></param>
        /// <param name="TemplatePath"></param>
        /// <param name="DataSource"></param>
        /// <param name="FileName"></param>
        /// <param name="Foot"></param>
        public static void ExcelOutToPrint(HttpContextBase Context, String TemplatePath, bool Merge, DataTable DataSource, String FileName, DataTable Foot)
        {
            HSSFWorkbook wb = CreateNPIOExcel(TemplatePath, Merge, DataSource, Foot);
            Context.Response.Charset = "UTF-8";
            Context.Response.ContentType = "application/vnd.ms-excel";
            Context.Response.ContentEncoding = Encoding.UTF8;
            //判断浏览器
            string UserAgent = Context.Request.ServerVariables["http_user_agent"].ToLower();
            FileName = DateTime.Now.ToString("yyyy-MM-dd") + FileName;
            if (UserAgent.IndexOf("firefox") == -1)
            {//非火狐浏览器
                FileName = HttpUtility.UrlEncode(FileName, Encoding.UTF8);
            }
            Context.Response.AddHeader("Content-Disposition", string.Format("attachment;filename={0}", FileName + ".xls"));
            Context.Response.Clear();

            System.IO.MemoryStream file = new System.IO.MemoryStream();
            wb.Write(file);
            file.WriteTo(Context.Response.OutputStream);
            Context.Response.End();
        }

        public static void ExcelOutToFile(string filePath, DataTable DataSource, string SheetName, string Title,DataTable Foot)
        {            
            HSSFWorkbook wb = CreateNPIOExcel(SheetName, Title, DataSource, Foot);
            using (FileStream fs = new FileStream(filePath, FileMode.OpenOrCreate, FileAccess.ReadWrite))
            {
                wb.Write(fs);
            }           
        }

        #endregion
    }
}
